﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;

namespace Analysis.DAL.Repositories
{
    /// <summary>
    /// Доступ к данным для приложения
    /// </summary>
    public class DataAccess
    {
        private static string ConnectionString = ConfigurationManager.ConnectionStrings["Analysis"].ConnectionString;

        private static SqlConnection GetConnection()
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return conn;
        }
                
        public static int ExecuteProcedure(string strdProc)
        {
            return ExecuteProcedure(strdProc, null, null);
        }

        public static int ExecuteProcedure(string strdProc, string[] parameters, object[] values)
        {
            return ExecuteProcedure(strdProc, parameters, values, null);
        }

        public static int ExecuteProcedure(string strdProc, string[] parameters, object[] values, SqlParameter[] outParam)
        {
            int rows = 0;

            using (var cn = DataAccess.GetConnection())
            {
                SqlCommand cmd = new SqlCommand(strdProc, cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 300;

                if (parameters != null)
                    for (int i = 0; i < parameters.Length; i++)
                        cmd.Parameters.AddWithValue(parameters[i], values[i]);

                if (outParam != null)
                    cmd.Parameters.AddRange(outParam);

                try
                {
                    rows = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                return rows;
            }
        }

        public static int ExecuteSQL(string sql)
        {
            int rows = 0;

            using (var cn = DataAccess.GetConnection())
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 300;

                try
                {
                    rows = cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    throw ex;
                }

                return rows;
            }
        }

        public static int ExecuteSQL(string strSQL, string[] parameters, object[] values)
        {
            int rows = 0;

            using (var cn = DataAccess.GetConnection())
            {
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 300;

                if (parameters != null)
                    for (int i = 0; i < parameters.Length; i++)
                        cmd.Parameters.AddWithValue(parameters[i], values[i]);

                try
                {
                    rows = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                return rows;
            }
        }

        public static DataTable GetDataTable(string sql)
        {
            return GetDataTable(sql, null, null);
        }

        public static DataTable GetDataTable(string sql, string[] parameters, object[] values)
        {
            using (var cn = DataAccess.GetConnection())
            {
                DataTable dt = new DataTable();
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 300;

                if (parameters != null)
                    for (int i = 0; i < parameters.Length; i++)
                        cmd.Parameters.AddWithValue(parameters[i], values[i]);

                try
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                return dt;
            }
        }
    }
}